Re: [GENERAL] problems with a sub-select (takes donkeys' years)

At 18:32 +0200 on 29/03/1999, Stuart Rison wrote:


> Can anyone explain why the sub-query form takes so long?

Basically, when you do the separation yourself, it means you executed the
subquery once, and then use it as a constant for another query. When you
combine them together, the internal select is ran over and over again,
because there is no way for the optimiser to know that the result will be
the same in all runs...

How about trying a different approach, such as:

SELECT brecard_id,count(brecard_id)
FROM malignant_pathologies p1
WHERE 3 = (
    SELECT count(*)
    FROM malignant_pathologies p2
    WHERE p2.brecard_id = p1.brecard_id
      AND code in ( 'MAPH', 'AMCA', 'LOCA' )
);

This may need tweaking, you are the one who knows distinctness. One of the
troubles of Postgres's SQL92 compatibility is its lack of support for
SELECT COUNT( DISTINCT code ) which is necessary if the combination of
brecard_id and code is not unique. Also, I'm not sure it allows constructs
such as WHERE (brecard_id,3) = ( SELECT brecard_id, count(*) FROM ....).
Gurus?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma